/********************************************************************************
 * Project Name		[JavaEE_JDBC]
 * File Name     	[TestStatement.java]
 * Creation Date 	[2015-01-01]
 * 
 * Copyright© ge.y.yang@gmail.com All Rights Reserved
 * 
 * Work hard, play harder, think big and keep fit
 ********************************************************************************/
package jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import utils.jdbc.JdbcUtils;

/**
 * java.sql.Statement<br/>
 * java.sql.PreparedStatement<br/>
 * 
 * @author 不落的太阳(Sean Yang aka ShortPeace)
 * @version 1.0
 * @since jdk 1.8
 * 
 */
public class TestStatement {

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;

	@Before
	public void init() throws Exception {
		connection = JdbcUtils.getInstance().getConnection();
	}

	/**
	 * 测试DDL语句
	 */
	@Test
	public void testDDL() throws Exception {
		String sql = "CREATE TABLE user(id INT(10) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, "
				+ "password VARCHAR(50) NOT NULL, age INT(3) NOT NULL, gender VARCHAR(2) NOT NULL, "
				+ "birthday DATE NOT NULL, Description mediumtext, Image mediumblob)";

		statement = connection.createStatement();
		// execute返回true表示执行查询, 返回false表示执行DDL/DML
		statement.execute(sql);
	}

	/**
	 * 测试Insert语句
	 * 
	 * @throws Exception
	 */
	@Test
	public void testInsert() throws Exception {
		String sql = "INSERT INTO user(username, password, age, gender, birthday) VALUES('Sean', '12345', 30, 'M', '1984-03-24')";
		statement = connection.createStatement();
		statement.executeUpdate(sql);
	}

	/**
	 * 测试Select语句
	 * 
	 * @throws Exception
	 */
	@Test
	public void testQuery() throws Exception {
		String sql = "SELECT * FROM user";
		statement = connection.createStatement();
		resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			System.out.print(resultSet.getString("username") + "-");
			System.out.print(resultSet.getInt("age") + "-");
			System.out.print(resultSet.getString("gender") + "-");
			System.out.println(resultSet.getDate("birthday"));
		}
	}

	/**
	 * 保存BLOB/CLOB
	 * 
	 * @throws Exception
	 */
	@Test
	public void testSaveBigData() throws Exception {
		// 读取二进制文件(BLOB)
		InputStream blobStream = TestStatement.class.getClassLoader().getResourceAsStream("resources/1.jpg");
		// 读取文本文件(CLOB)
		File clobFile = new File("src/resources/description.txt");
		BufferedReader clobReader = new BufferedReader(new FileReader(clobFile));

		String sql = "INSERT INTO User(username, password, age, gender, birthday, image, description) VALUES (?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, "Yang");
		preparedStatement.setString(2, "12345");
		preparedStatement.setInt(3, 30);
		preparedStatement.setString(4, "M");
		preparedStatement.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
		// 将BLOB数据存入数据库
		preparedStatement.setBinaryStream(6, blobStream);
		// 将CLOB数据存入数据库, 如果读入的数据仅仅包含ASCII字符, 可以使用setAsciiStream()方法
		preparedStatement.setCharacterStream(7, clobReader, clobFile.length());
		preparedStatement.executeUpdate();

		statement = preparedStatement;
	}

	/**
	 * 查询BLOB/CLOB
	 * 
	 * @throws Exception
	 */
	@Test
	public void testReadBigData() throws Exception {
		// 处理BLOB数据的流
		InputStream blobIn = null;
		OutputStream blobOut = new FileOutputStream(new File("src/resources/2.jpg"));

		// 处理CLOB数据的流
		Reader clobIn = null;
		Writer clobOut = new PrintWriter(new FileWriter("src/resources/clob.txt"));

		String sql = "SELECT * FROM User WHERE ID = 2";
		statement = connection.createStatement();
		resultSet = statement.executeQuery(sql);

		while (resultSet.next()) {
			// 读取BLOB
			Blob blob = resultSet.getBlob("image");
			blobIn = blob.getBinaryStream();

			byte[] blobBuffer = new byte[1024];
			int length = 0;
			while ((length = blobIn.read(blobBuffer)) != -1) {
				blobOut.write(blobBuffer, 0, length);
			}

			// 读取CLOB
			Clob clob = resultSet.getClob("description");
			clobIn = clob.getCharacterStream();

			char[] clobBuffer = new char[1024];
			length = 0;
			while ((length = clobIn.read(clobBuffer)) != -1) {
				clobOut.write(clobBuffer, 0, length);
			}

			System.out.print(resultSet.getString("username") + "\t" + resultSet.getInt("age") + "\t"
					+ resultSet.getString("gender"));
		}
		clobOut.close();
		clobIn.close();
		blobOut.close();
		blobIn.close();
	}

	@After
	public void destroy() {
		JdbcUtils.getInstance().close(resultSet, statement, connection);
	}
}
